package com.erp.app.catalog.itemMaster.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.common.ProjectConstants;
import com.erp.app.catalog.itemMaster.criteria.ItemMasterCriteria;

import com.erp.app.catalog.itemMaster.pk.ItemMasterPk;
import com.erp.app.catalog.itemMaster.vo.ItemMasterVO;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.dao.SimpleSearchMaintStyleDao;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomSqlDate;
import com.erp.framework.util.CommonUtil;


public class ItemMasterDao extends SimpleSearchMaintStyleDao {
	
	protected String sqlQuery(SimpleBaseSrchCriteria obj) {

		ItemMasterCriteria criteria = (ItemMasterCriteria) obj;
		String whereClause = toWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "";
        String sql = null;
		if(!criteria.isSearchResult()){
			sql = "SELECT DISTINCT item_hdr.ITEM_ID, item_hdr.NODE_ID, item_hdr.ACTICLE_NUMBER, item_hdr.CREATE_COMPANY_CODE "
				+ ", item_hdr.OWNER_COMPANY_CODE, item_hdr.ITEM_DESCRIPTION, item_hdr.WATER_MARK "
				+ ", item_pic.FILE_ID T_ITEM_HEADERIMGID, file_man.FILE_BASE_PATH, file_man.FILE_NAME, item_hdr.BROWSER "
				+ " FROM T_ITEM_HEADER item_hdr, T_PICTURES item_pic, T_PICTURE_LEVEL item_lvl "
				+ " , T_FILE_MANAGER file_man, T_CATEGORY_TREE cat_tree " 			
    			+ whereClause
    			+ groupByClause
    			+ orderByClause;
		} else {
			sql = "SELECT DISTINCT item_hdr.ITEM_ID, item_hdr.NODE_ID, item_hdr.ACTICLE_NUMBER, item_hdr.CREATE_COMPANY_CODE "
				+ ", item_hdr.OWNER_COMPANY_CODE, item_hdr.ITEM_DESCRIPTION, item_hdr.WATER_MARK "
				+ ", item_pic.FILE_ID T_ITEM_HEADERIMGID, file_man.FILE_BASE_PATH, file_man.FILE_NAME, item_hdr.BROWSER "
				+ " FROM T_ITEM_HEADER item_hdr, T_PICTURES item_pic, T_PICTURE_LEVEL item_lvl "
				+ " , T_FILE_MANAGER file_man, T_CATEGORY_TREE cat_tree "           
                + whereClause
                + groupByClause
                + orderByClause;
		}
		super.logSqlQuery(sql);
		return sql;
	}

	protected String toWhereClause(ItemMasterCriteria crit) {

		ItemMasterCriteria criteria = (ItemMasterCriteria) crit;
		StringBuffer whereClause = new StringBuffer();
		
		whereClause.append(" WHERE item_hdr.BROWSER<>0 AND item_pic.ITEM_ID=item_lvl.ITEM_ID(+) AND item_pic.IMAGE_ID=item_lvl.IMAGE_ID(+)"
				+ " AND 2=item_lvl.IMAGE_LEVEL_ID(+) AND item_lvl.FILE_ID=file_man.FILE_ID(+) "
				+ " AND item_hdr.ITEM_ID=item_pic.ITEM_ID(+) AND item_hdr.ITEM_ID = cat_tree.ITEM_ID "
				+ " AND (item_pic.IMAGE_ID = (SELECT MIN(a.IMAGE_ID) FROM T_PICTURES a WHERE a.ITEM_ID=item_pic.ITEM_ID) OR item_pic.IMAGE_ID IS NULL )");
		
        if (criteria.getItemId() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("ITEM_ID = '" + criteria.getItemId().trim().toUpperCase() + "' ");
        }
        if (criteria.getArticleNo() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("ACTICLE_NUMBER LIKE '%" + criteria.getArticleNo().trim().toUpperCase() + "%' ");
        }
        if (criteria.getItemDesc() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("ITEM_DESCRIPTION LIKE '%" + criteria.getItemDesc().trim().toUpperCase() + "%' ");
        }
        if (criteria.getNodeIdList() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("NODE_ID IN (" + criteria.getNodeIdList().trim().toUpperCase() + ") ");
        }
		return whereClause.toString();
	}

	public ChildList selectByParent(SimpleBaseVo parent, Connection conn)
		throws FindRecordException {

		return null;
	}

	protected String sqlInsert() {

		String sql = "";

		super.logSqlQuery(sql);
		return sql;
	}

	protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
		throws SQLException {

		ItemMasterVO vo = (ItemMasterVO) obj;
		int i = 1;

		pStmt.setString(i++, vo.getCompanyCode());		
	}

	protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
	}

	protected String sqlUpdate() {

		String sql = "";

		//super.logSqlQuery(sql);
		return sql;
	}

	protected void setValuesForModify(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
	}

	protected String sqlDelete() {
		String sql = "";

		//super.logSqlQuery(sql);
		return sql;
	}

	protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
		throws SQLException {

		ItemMasterVO vo = (ItemMasterVO) obj;
		int i = 1;	
	}

	protected String sqlFind() {
        String sql = "SELECT DISTINCT item_hdr.ITEM_ID, item_hdr.NODE_ID, item_hdr.ACTICLE_NUMBER, item_hdr.CREATE_COMPANY_CODE "
						+ ", item_hdr.OWNER_COMPANY_CODE, item_hdr.ITEM_DESCRIPTION, item_hdr.WATER_MARK "
						+ ", item_pic.FILE_ID T_ITEM_HEADERIMGID, file_man.FILE_BASE_PATH, file_man.FILE_NAME, item_hdr.BROWSER "
				   + " FROM T_ITEM_HEADER item_hdr, T_PICTURES item_pic, T_PICTURE_LEVEL item_lvl, T_FILE_MANAGER file_man "
						+ " ,T_CATEGORY_TREE cat_tree "
				   + " WHERE item_hdr.BROWSER<>0 AND item_pic.ITEM_ID=item_lvl.ITEM_ID(+) AND item_pic.IMAGE_ID=item_lvl.IMAGE_ID(+)"
						+ " AND 2=item_lvl.IMAGE_LEVEL_ID(+) AND item_lvl.FILE_ID=file_man.FILE_ID(+) "
						+ " AND item_hdr.ITEM_ID=item_pic.ITEM_ID(+) AND item_hdr.ITEM_ID = cat_tree.ITEM_ID "
						+ " AND (item_pic.IMAGE_ID = (SELECT MIN(a.IMAGE_ID) FROM T_PICTURES a WHERE a.ITEM_ID=item_pic.ITEM_ID) OR item_pic.IMAGE_ID IS NULL )"
						+ " AND ITEM_HDR.ITEM_ID=? "; 
        this.logSqlQuery(sql);
        return sql;
	}


	protected void setValuesForFind(PreparedStatement pStmt, Object obj)
		throws SQLException {

		ItemMasterPk pk = (ItemMasterPk) obj;
		int i = 1;
		pStmt.setBigDecimal(i++, new BigDecimal(pk.getItemId()));
	}


	protected String sqlList() {
		String sql = "SELECT DISTINCT item_hdr.ITEM_ID, item_hdr.NODE_ID, item_hdr.ACTICLE_NUMBER, item_hdr.CREATE_COMPANY_CODE "
			+ ", item_hdr.OWNER_COMPANY_CODE, item_hdr.ITEM_DESCRIPTION, item_hdr.WATER_MARK "
			+ ", item_pic.FILE_ID T_ITEM_HEADERIMGID, file_man.FILE_BASE_PATH, file_man.FILE_NAME, item_hdr.BROWSER "
	   + " FROM T_ITEM_HEADER item_hdr, T_PICTURES item_pic, T_PICTURE_LEVEL item_lvl, T_FILE_MANAGER file_man "
			+ " ,T_CATEGORY_TREE cat_tree "
	   + " WHERE item_hdr.BROWSER<>0 AND item_pic.ITEM_ID=item_lvl.ITEM_ID(+) AND item_pic.IMAGE_ID=item_lvl.IMAGE_ID(+)"
			+ " AND 2=item_lvl.IMAGE_LEVEL_ID(+) AND item_lvl.FILE_ID=file_man.FILE_ID(+) "
			+ " AND item_hdr.ITEM_ID=item_pic.ITEM_ID(+) AND item_hdr.ITEM_ID = cat_tree.ITEM_ID "
			+ " AND (item_pic.IMAGE_ID = (SELECT MIN(a.IMAGE_ID) FROM T_PICTURES a WHERE a.ITEM_ID=item_pic.ITEM_ID) OR item_pic.IMAGE_ID IS NULL )"
			+ " AND ITEM_HDR.ITEM_ID=? "; 
		this.logSqlQuery(sql);
		return sql;		
	}

	protected void setValuesForList(PreparedStatement pStmt, Object obj)
		throws SQLException {
		ItemMasterPk pk = (ItemMasterPk) obj;
		int i = 1;
		pStmt.setBigDecimal(i++, new BigDecimal(pk.getItemId()));
	}

	protected SimpleBaseVo getSearchResultSetValues(ResultSet rs) throws SQLException {
        ItemMasterVO vo = new ItemMasterVO();
        vo.setItemId(CommonUtil.isEmpty(rs.getBigDecimal("ITEM_ID"))?"":rs.getBigDecimal("ITEM_ID").toString());
		vo.setNodeId(CommonUtil.isEmpty(rs.getBigDecimal("NODE_ID"))?"":rs.getBigDecimal("NODE_ID").toString());				
		vo.setActicleNumber(rs.getString("ACTICLE_NUMBER"));				
		vo.setCreateCompanyCode(rs.getString("CREATE_COMPANY_CODE"));				
		vo.setOwnerCompanyCode(rs.getString("OWNER_COMPANY_CODE"));				
		vo.setItemDesc(rs.getString("ITEM_DESCRIPTION"));				
		vo.setWaterMark(CommonUtil.isEmpty(rs.getBigDecimal("WATER_MARK"))?"":rs.getBigDecimal("WATER_MARK").toString());				
		vo.setItemHdrImgId(rs.getString("T_ITEM_HEADERIMGID"));				
		vo.setImagePath(rs.getString("FILE_BASE_PATH"));				
		vo.setImageName(rs.getString("FILE_NAME"));
		vo.setBrowser(CommonUtil.isEmpty(rs.getBigDecimal("BROWSER"))?"":rs.getBigDecimal("BROWSER").toString());
		return vo;
	}

	protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {
		ItemMasterVO vo = new ItemMasterVO();
		vo.setItemId(CommonUtil.isEmpty(rs.getBigDecimal("ITEM_ID"))?"":rs.getBigDecimal("ITEM_ID").toString());
		vo.setNodeId(CommonUtil.isEmpty(rs.getBigDecimal("NODE_ID"))?"":rs.getBigDecimal("NODE_ID").toString());				
		vo.setActicleNumber(rs.getString("ACTICLE_NUMBER"));				
		vo.setCreateCompanyCode(rs.getString("CREATE_COMPANY_CODE"));				
		vo.setOwnerCompanyCode(rs.getString("OWNER_COMPANY_CODE"));				
		vo.setItemDesc(rs.getString("ITEM_DESCRIPTION"));				
		vo.setWaterMark(CommonUtil.isEmpty(rs.getBigDecimal("WATER_MARK"))?"":rs.getBigDecimal("WATER_MARK").toString());				
		vo.setItemHdrImgId(rs.getString("T_ITEM_HEADERIMGID"));				
		vo.setImagePath(rs.getString("FILE_BASE_PATH"));				
		vo.setImageName(rs.getString("FILE_NAME"));
		vo.setBrowser(CommonUtil.isEmpty(rs.getBigDecimal("BROWSER"))?"":rs.getBigDecimal("BROWSER").toString());
		return vo;
	}
	
	protected void test(){
		
	}

}
